//
//  MovieDataSingTon.m
//  newDouban
//
//  Created by dllo on 15/11/3.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "MovieDataSingTon.h"
#import <sqlite3.h>
@implementation MovieDataSingTon


+ (instancetype)shareMovieDataBase
{
    static MovieDataSingTon *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[MovieDataSingTon alloc] init];
    }
    return dataBase;
}


//声明数据库对象
static sqlite3 *db = nil;  //(sqlite3无联想,硬打)
//sqlite数据库是基于c语言的底层数据库

//打开数据库()
- (void)openDB
{
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    /**
     *打开数据库
     参数1,数据库文件路径
     参数2.数据库对象
     */
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"movie.sqlite"];
    
    NSLog(@"moviesqlPath = %@", sqlPath);
    
    BOOL result = sqlite3_open(sqlPath.UTF8String, &db);//成功返回的是零
    //SQLITE_OK相当于内部的零
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"失败");
    }
}


//打开数据库()
- (void)openDBDT
{
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    /**
     *打开数据库
     参数1,数据库文件路径
     参数2.数据库对象
     */
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"movieDT.sqlite"];
    
    NSLog(@"moviesqlPath = %@", sqlPath);
    
    BOOL result = sqlite3_open(sqlPath.UTF8String, &db);//成功返回的是零
    //SQLITE_OK相当于内部的零
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"失败");
    }
}




//创建表
- (void)createTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS movie0920(number INTEGER PRIMARY KEY AUTOINCREMENT, myMovieId TEXT, movieName TEXT, pic_url TEXT)";
    BOOL result1 = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result1 == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}

//插入一个电影model
- (void)insertMovie:(Movie *)movie
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO movie0920(myMovieId, movieName, pic_url)VALUES('%@', '%@', '%@')", movie.myMovieId, movie.movieName, movie.pic_url];
    //执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
}


//查询所有学生
- (NSMutableArray *)selectMovie
{
    
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = @"SELECT * FROM movie0920";
    
    //创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    //查询前的准备工作
    //参数3.设置限制sql语句长度,-1 为不限制
    //将数据库对象db,sql语句,跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    } else {
        NSLog(@"查询失败");
    }
    
    //循环遍历每一行数据(用指针指向每一行)
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //取出第一列数据
        int number = sqlite3_column_int(stmt, 0);
        //取出第二列数据
        const unsigned char * myMovieId = sqlite3_column_text(stmt, 1);//点进去看该返回什么数据类型
        const unsigned char * movieName = sqlite3_column_text(stmt, 2);
        const unsigned char * pic_url = sqlite3_column_text(stmt, 3);
        
        
        //将c予以类型转成oc类型
        //
        NSInteger num = number;
        NSString *myMovieIdStr = [NSString stringWithUTF8String:(const char *)myMovieId];
        NSString *movieNameStr = [NSString stringWithUTF8String:(const char *)movieName];
        NSString *pic_urlStr = [NSString stringWithUTF8String:(const char *)pic_url];
        
        
        Movie *movie = [[Movie alloc] init];
        movie.number = num;
        movie.myMovieId = myMovieIdStr;
        movie.movieName = movieNameStr;
        movie.pic_url = pic_urlStr;
        [array addObject:movie];
        [movie release];
    }
    
    return array;
    
}



- (void)createTableDT
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS movieDT0920(number INTEGER PRIMARY KEY AUTOINCREMENT, rating TEXT, release_date TEXT, runtime TEXT, genres TEXT, country TEXT, actors TEXT, plot_simple TEXT, rating_count TEXT, title TEXT, film_locations INTEGER)";
    BOOL result1 = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result1 == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}


- (void)insertMovieDT:(MovieDetail *)movie
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO movieDT0920(myMovieId, movieName, pic_url)VALUES('%@', '%@', '%@', '%@','%@', '%@', '%@', '%@','%@', '%@')", movie.rating, movie.release_date, movie.runtime, movie.genres, movie.country, movie.actors, movie.plot_simple, movie.rating_count, movie.title, movie.film_locations];
    //执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
}



//查询所有学生
- (NSMutableArray *)selectMovieDT
{
    
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = @"SELECT * FROM movieDT0920";
    
    //创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    //查询前的准备工作
    //参数3.设置限制sql语句长度,-1 为不限制
    //将数据库对象db,sql语句,跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    } else {
        NSLog(@"查询失败");
    }
    
    //循环遍历每一行数据(用指针指向每一行)
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //取出第一列数据
        int number = sqlite3_column_int(stmt, 0);
        //取出第二列数据
        const unsigned char * rating = sqlite3_column_text(stmt, 1);//点进去看该返回什么数据类型
        const unsigned char * release_date = sqlite3_column_text(stmt, 2);
        const unsigned char * runtime = sqlite3_column_text(stmt, 3);
        const unsigned char * genres = sqlite3_column_text(stmt, 4);
        const unsigned char * country = sqlite3_column_text(stmt, 5);
        const unsigned char * actors = sqlite3_column_text(stmt, 6);
        const unsigned char * plot_simple = sqlite3_column_text(stmt, 7);
        const unsigned char * rating_count = sqlite3_column_text(stmt, 8);
        const unsigned char * title = sqlite3_column_text(stmt, 9);
        const unsigned char * film_locations = sqlite3_column_text(stmt, 10);
        
        
        //将c予以类型转成oc类型
        //
        NSInteger num = number;
        NSString *ratingStr = [NSString stringWithUTF8String:(const char *)rating];
        NSString *release_dateStr = [NSString stringWithUTF8String:(const char *)release_date];
        NSString *runtimeStr = [NSString stringWithUTF8String:(const char *)runtime];
        NSString *genresStr = [NSString stringWithUTF8String:(const char *)genres];
        NSString *countryStr = [NSString stringWithUTF8String:(const char *)country];
        NSString *actorsStr = [NSString stringWithUTF8String:(const char *)actors];
        NSString *plot_simpleStr = [NSString stringWithUTF8String:(const char *)plot_simple];
        NSString *rating_countStr = [NSString stringWithUTF8String:(const char *)rating_count];
        NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
        NSString *film_locationsStr = [NSString stringWithUTF8String:(const char *)film_locations];
        
        
        MovieDetail *movie = [[MovieDetail alloc] init];
        movie.number = num;
        movie.rating = ratingStr;
        movie.release_date = release_dateStr;
        movie.runtime = runtimeStr;
        movie.genres = genresStr;
        movie.country = countryStr;
        movie.actors = actorsStr;
        movie.plot_simple = plot_simpleStr;
        movie.rating_count = rating_countStr;
        movie.title = titleStr;
        movie.film_locations = film_locationsStr;
        
        
        [array addObject:movie];
        [movie release];
    }
    
    return array;
    
}



//删除整张表
- (void)dropTable
{
    NSString *sql = @"DROP TABLE movie0920";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    } else {
        NSLog(@"删除表失败");
    }
}


- (void)dropTableDT
{
    NSString *sql = @"DROP TABLE movieDT0920";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    } else {
        NSLog(@"删除表失败");
    }
}


@end
